/****** Object:  StoredProcedure [dbo].[cms_sp_PageCopy]    Script Date: 23.07.2015 17:10:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[cms_sp_PageCopy] 
 (  
 @PageId int, 
 @CreatedBy int, 
 @DateCreated datetime, 
 @PageTypeParameter nvarchar(150), 
 @ContentTypeParameter nvarchar(150), 
 @PageContentTypeParameter nvarchar(150), 
 @PageControlTypeParameter nvarchar(150) 
 ) 
 as 
 declare @NewPageId int 
 declare @PageNodeId hierarchyid 
 declare @ParentPageNodeId hierarchyid 
 declare @PageTypeKey int 
 declare @SiteId int 
 declare @SiteGroupId int 
 declare @PageUrl varchar(250) 
 declare @PageUrlBase varchar(250) 
  
 select  
 	@ParentPageNodeId = CASE WHEN PageNodeId.GetLevel()>0 THEN PageNodeId.GetAncestor(1) ELSE hierarchyid::GetRoot() END, 
 	@PageTypeKey = PageTypeKey, 
 	@PageUrlBase = PageUrl + '-copy', 
 	@SiteId = SiteId, 
 	@SiteGroupId = SiteGroupId 
 from dbo.Pages 
 where PageId = @PageId 
  
 -- PageNodeId 
 select @PageNodeId = max(PageNodeId) from dbo.Pages 
 	where PageNodeId.GetAncestor(1) = @ParentPageNodeId 
  
 set @PageNodeId = @ParentPageNodeId.GetDescendant(@PageNodeId, null) 
  
 -- URL 
 declare @CopyIndex int = 1 
 set @PageUrl = @PageUrlBase 
  
 while (select COUNT(*)  
         from [dbo].[Pages] inner join [dbo].[EntityRevisions] 
 	    on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter 
         where (([SiteGroupId] is null and [SiteId] is null) or  
    			([SiteGroupId] = @SiteGroupId and [SiteId] is null) or 
 			([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId)) and  
 			EntityRevisions.IsLatestRevision = 1 and 
 			EntityRevisions.IsDeleted = 0 and 
 			Pages.PageUrl = @PageUrl)>0 
 begin 
    set @PageUrl = @PageUrlBase + CONVERT(varchar,@CopyIndex) 
    set @CopyIndex = @CopyIndex + 1 
 end 
  
 insert into dbo.Pages 
 ( 
 SiteGroupId, 
 SiteId, 
 SystemLanguageId, 
 TemplateId, 
 PageNodeId, 
 PublicPageId, 
 PageTypeKey, 
 PageBehaviourTypeKey, 
 PageName, 
 PageUrl, 
 IsVisible, 
 UseFileCache, 
 PageTitle, 
 PageTitleOverride, 
 PageDescription, 
 PageKeywords, 
 CreatedBy, 
 DateCreated, 
 PageKey 
 ) 
 select  
 SiteGroupId, 
 SiteId, 
 SystemLanguageId, 
 TemplateId, 
 @PageNodeId, 
 0, 
 PageTypeKey, 
 PageBehaviourTypeKey, 
 PageName + ' copy', 
 @PageUrl, 
 IsVisible, 
 UseFileCache, 
 PageTitle, 
 PageTitleOverride, 
 PageDescription, 
 PageKeywords, 
 @CreatedBy, 
 @DateCreated, 
 PageKey 
 from dbo.Pages 
 where PageId=@PageId 
 set @NewPageId = SCOPE_IDENTITY() 
  
 update dbo.Pages 
 	set PublicPageId = @NewPageId 
 where PageId = @NewPageId 
  
 insert into dbo.EntityRevisions 
 ( 
 EntityId, 
 PublicEntityId, 
 EntityTypeParameter, 
 WorkflowId, 
 IsLatestRevision, 
 IsLatestCompletedRevision, 
 IsDeleted, 
 IsPublished 
 ) 
 values 
 ( 
 @NewPageId, 
 @NewPageId, 
 @PageTypeParameter, 
 null, 
 1, 
 0, 
 0, 
 0 
 ) 
 -- copy all page's properties 
 declare @EntityMetaFieldLinkId int 
 declare @EntityMetaValue nvarchar(max) 
 declare page_property_cursor cursor for  
 select 
 	EntityMetas.EntityMetaFieldLinkId, 
 	EntityMetas.EntityMetaValue 
 from dbo.EntityMetas 
 inner join dbo.EntityMetaFieldLinks 
 	on EntityMetaFieldLinks.EntityMetaFieldLinkId = EntityMetas.EntityMetaFieldLinkId and 
 		EntityMetaFieldLinks.EntityTypeParameter = @PageTypeParameter 
 where EntityId = @PageId 
 open page_property_cursor; 
 fetch next from page_property_cursor  
 	into @EntityMetaFieldLinkId, @EntityMetaValue 
 while @@fetch_status = 0 
 begin 
 	insert into dbo.EntityMetas 
 	( 
 	EntityMetaFieldLinkId, 
 	EntityId, 
 	EntityMetaValue 
 	) 
 	values 
 	( 
 	@EntityMetaFieldLinkId, 
 	@NewPageId, 
 	@EntityMetaValue 
 	) 
 fetch next from page_property_cursor  
 	into @EntityMetaFieldLinkId, @EntityMetaValue 
 end 
 close page_property_cursor 
 deallocate page_property_cursor 
 -- copy all page's controls 
 declare @PageControlId int 
 declare @TemplateRegionId int 
 declare @ModuleId int 
 declare @PageControlTypeKey int 
 declare @PageControlContentId int 
 declare @PageControlViewValue xml 
 declare @PageControlModelValue xml 
 declare @PageControlOrder int 
 declare @ContentId int 
 declare @ContentTypeId int 
 select  
 	@ContentTypeId = ContentTypeId  
 from [dbo].[ContentTypes] 
 where ContentTypeName = @PageContentTypeParameter 
 declare page_control_cursor cursor for  
 select  
 	PageControlId,	 
 	TemplateRegionId, 
 	ModuleId, 
 	PageControlTypeKey, 
 	PageControlContentId, 
 	PageControlViewValue, 
 	PageControlModelValue, 
 	PageControlOrder 
 from dbo.PageControls 
 inner join dbo.EntityRevisions 
 	on EntityRevisions.EntityId = PageControls.PageControlId and  
 	EntityRevisions.EntityTypeParameter = @PageControlTypeParameter and 
 	EntityRevisions.IsLatestRevision = 1 
 where PageId = @PageId 
 open page_control_cursor; 
 fetch next from page_control_cursor  
 	into  
 		@PageControlId,		 
 		@TemplateRegionId, 
 		@ModuleId, 
 		@PageControlTypeKey, 
 		@PageControlContentId, 
 		@PageControlViewValue, 
 		@PageControlModelValue, 
 		@PageControlOrder 
 while @@fetch_status = 0 
 begin 
 	if @PageControlTypeKey = 1 -- page content 
 	begin 
 		insert into dbo.Contents 
 		( 
 		SiteId, 
 		SiteGroupId, 
 		SystemLanguageId, 
 		ContentTypeId, 
 		PublicContentId, 
 		ContentTitle, 
 		ContentBody, 
 		CreatedBy, 
 		DateCreated 
 		) 
 		select  
 			@SiteId, 
 			@SiteGroupId, 
 			SystemLanguageId, 
 			@ContentTypeId, 
 			0, 
 			ContentTitle, 
 			ContentBody, 
 			@CreatedBy, 
 			@DateCreated 
 		from dbo.Contents 
 		where ContentId = @PageControlContentId 
 		set @ContentId = SCOPE_IDENTITY() 
 		update dbo.Contents 
 			set PublicContentId = @ContentId 
 		where ContentId = @ContentId 
 		insert into dbo.EntityRevisions 
 		( 
 		EntityId, 
 		PublicEntityId, 
 		EntityTypeParameter, 
 		WorkflowId, 
 		IsLatestRevision, 
 		IsLatestCompletedRevision, 
 		IsDeleted, 
 		IsPublished 
 		) 
 		values 
 		( 
 		@ContentId, 
 		@ContentId, 
 		@ContentTypeParameter, 
 		null, 
 		1, 
 		0, 
 		0, 
 		0 
 		) 
 	end 
 	insert into dbo.PageControls 
 	( 
 	PageId, 
 	TemplateRegionId, 
 	ModuleId, 
 	PageControlContentId, 
 	PageControlTypeKey, 
 	PageControlViewValue, 
 	PageControlModelValue, 
 	PageControlOrder, 
 	CreatedBy, 
 	DateCreated 
 	) 
 	values 
 	(  
 	@NewPageId, 
 	@TemplateRegionId, 
 	@ModuleId, 
 	(case @PageControlTypeKey 
 		when 1 then @ContentId 
 		else @PageControlContentId 
 	end), 
 	@PageControlTypeKey, 
 	@PageControlViewValue, 
 	@PageControlModelValue, 
 	@PageControlOrder, 
 	@CreatedBy, 
 	@DateCreated 
 	) 
 	set @PageControlId = SCOPE_IDENTITY() 
 	insert into dbo.EntityRevisions 
 	( 
 	EntityId, 
 	PublicEntityId, 
 	EntityTypeParameter, 
 	WorkflowId, 
 	IsLatestRevision, 
 	IsLatestCompletedRevision, 
 	IsDeleted, 
 	IsPublished 
 	) 
 	values 
 	( 
 	@PageControlId, 
 	@PageControlId, 
 	@PageControlTypeParameter, 
 	null, 
 	1, 
 	0, 
 	0, 
 	0 
 	) 
 fetch next from page_control_cursor  
 	into  
 		@PageControlId,		 
 		@TemplateRegionId, 
 		@ModuleId, 
 		@PageControlTypeKey, 
 		@PageControlContentId, 
 		@PageControlViewValue, 
 		@PageControlModelValue, 
 		@PageControlOrder 
 end 
 close page_control_cursor 
 deallocate page_control_cursor 
  
 exec cms_sp_PageSelectOne @PageId=@NewPageId, @PublicPageId=null, @SiteGroupId=@SiteGroupId, @SiteId=@SiteId, @SystemLanguageId=null, @PageTypeParameter=@PageTypeParameter, @PageControlTypeParameter=@PageControlTypeParameter, @IsInAdministration=1 

GO


